package com.itfreer.gis.locus.data;


import com.itfreer.form.api.JdbcBaseDaoImp;
import com.itfreer.gis.locus.data.dao.LocusWorkDao;
import com.itfreer.gis.locus.data.dto.WorkSitutationDTO;
import com.itfreer.gis.locus.data.entity.LocusWorkEntity;
import org.hibernate.query.internal.NativeQueryImpl;
import org.hibernate.transform.Transformers;
import org.springframework.stereotype.Component;

import javax.persistence.EntityManager;
import javax.persistence.Query;

import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.List;
import java.util.Map;

/**
 * 定义巡护任务 jdbc实现类
 */
@Component
public class JdbcLocusWorkDao extends JdbcBaseDaoImp<LocusWorkEntity> implements LocusWorkDao {

	public static final Integer distance = 5000;

	public static final Integer timeout = 180;

	public JdbcLocusWorkDao(EntityManager entityManager) {
		super(LocusWorkEntity.class, entityManager);
	}

	@Override
	public Map<String, Object> getDataSituation(WorkSitutationDTO workSitutationDTO) {
		String sql = "select count(id) as locusCount, sum(d_distance) as distance, sum(d_timecount) as timecount\n" +
				"from gis_locus_data\n" +
				"where 1 = 1\n";
		if(workSitutationDTO.getOwerid() != null) {
			sql += "and s_owerid = \'" + workSitutationDTO.getOwerid() + "\'\n";
		}

		if (workSitutationDTO.getStartTime() != null) {
			sql += "and d_begintime >= \'" + workSitutationDTO.getStartTime() + "\'\n";
		}

		if (workSitutationDTO.getEndTime() != null) {
			sql += "and d_begintime <= \'" + workSitutationDTO.getEndTime() + "\'";
		}
		Query query = em.createNativeQuery(sql);
		query.unwrap(NativeQueryImpl.class).setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP);
		List<Map<String, Object>> list = query.getResultList();
		return list.get(0);
	}

	@Override
	public List<Map<String, Object>> getDataSituationByState(WorkSitutationDTO workSitutationDTO) {
		String sql = "";
		switch (workSitutationDTO.getState()) {
			case 1:
				sql += "select to_char(d_begintime::::DATE, 'YYYY') as date, ";
				break;
			case 2:
				sql += "select to_char(d_begintime::::DATE, 'YYYY-MM') as date, ";
				break;
			case 3:
				sql += "select to_char(d_begintime::::DATE-(extract(dow from d_begintime::::TIMESTAMP)-1||'day')::::interval, 'YYYY-mm-dd') date,";
				break;
			case 4:
				sql += "select to_char(d_begintime::::DATE, 'YYYY-MM-DD') as date, ";
				break;
			case 5:
				sql += "select ";
				break;
			default:
				sql += "select ";
				break;
		}
		sql = sql + " count(*) as locuscount,sum(d_distance) as distance, sum(d_timecount) as timecount \n from gis_locus_data\n" +
				"where 1 = 1\n";
		if(workSitutationDTO.getOwerid() != null) {
			sql += "and s_owerid = \'" + workSitutationDTO.getOwerid() + "\'\n";
		}
		
		SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
		if (workSitutationDTO.getStartTime() != null) {
			String start = sdf.format(workSitutationDTO.getStartTime());
			String startTime = "to_date(\'"+ start + "\',\'YYYY-MM-DD hh24:mi:ss\')";
			sql += "and d_begintime >= " + startTime + "\n";
		}

		if (workSitutationDTO.getEndTime() != null) {
			String end = sdf.format(workSitutationDTO.getEndTime());
			String endTime = "to_date(\'"+ end + "\',\'YYYY-MM-DD hh24:mi:ss\')";
			sql += "and d_begintime <= " + endTime + "";
		}
		sql += "and d_distance > " + distance +"\n" +
				"and d_timecount > "+ timeout +"\n";
		if (workSitutationDTO.getState() != 0 && workSitutationDTO.getState() != 5) {
		sql += "group by date \n" +
					"order by date";
		}

		Query query = em.createNativeQuery(sql);
		query.unwrap(NativeQueryImpl.class).setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP);
		List<Map<String, Object>> list = query.getResultList();
		return list;
	}


}
